﻿ALTER TABLE Customer
ADD MessageSettings BIGINT NULL

ALTER TABLE LogisticsPackage
ADD PackageNoteVersion INT NOT NULL DEFAULT (0)

ALTER TABLE LogisticsPackageNote
ADD ShippingStatusId INT NULL

ALTER TABLE LogisticsPackageNote
ADD AddressId INT NULL
CREATE NONCLUSTERED INDEX [IX_PackageNoteAddressId] ON [dbo].[LogisticsPackageNote]([AddressId] ASC);


INSERT INTO ActivityLogType VALUES ('CancelOrder', 'Cancel an order', 1) 

ALTER TABLE LogisticsPlan ALTER COLUMN CourierId INT NULL

ALTER TABLE LogisticsPlan ADD CustomerId INT NULL
ALTER TABLE LogisticsPlan ADD Name NVARCHAR(MAX) NULL
CONSTRAINT [LogisticsPlan_Customer] FOREIGN KEY ([CustomerId]) REFERENCES [dbo].[Customer] ([Id])

CREATE TABLE [dbo].[Holiday_StateProvince_Mapping] (
    [Holiday_Id]       INT NOT NULL,
    [StateProvince_Id] INT NOT NULL,
    PRIMARY KEY CLUSTERED ([Holiday_Id] ASC, [StateProvince_Id] ASC),
    CONSTRAINT [Holiday_Provinces] FOREIGN KEY ([Holiday_Id]) REFERENCES [dbo].[Holiday] ([Id]) ON DELETE CASCADE,
    CONSTRAINT [Provinces_Holiday] FOREIGN KEY ([StateProvince_Id]) REFERENCES [dbo].[StateProvince] ([Id]) ON DELETE CASCADE
);

--SET IDENTITY_INSERT [dbo].[Region] ON
--INSERT INTO [dbo].[Region] ([Id], [Name], [DisplayOrder]) VALUES (3, N'Edmonton', 2)
--SET IDENTITY_INSERT [dbo].[Region] OFF

--SET IDENTITY_INSERT [dbo].[LogisticsDistrict] ON
--INSERT INTO [dbo].[LogisticsDistrict] ([Id], [RegionId], [StateProvinceId], [Name], [DisplayOrder], [CountryCode], [Code], [TimeZoneId]) VALUES (3, 3, 1, N'Edmonton', 2, N'CA', N'EDM', N'Mountain Standard Time')
--SET IDENTITY_INSERT [dbo].[LogisticsDistrict] OFF

--DELETE FROM [dbo].[LogisticsZone];
--SET IDENTITY_INSERT [dbo].[LogisticsZone] ON
--INSERT INTO [dbo].[LogisticsZone] ([Id], [StateProvinceId], [ZipPostalCode], [Name], [CountryCode], [DisplayOrder], [DistrictId], [ZoneGroupId]) VALUES (1, NULL, N'V7W|V7S|V7V|V7T|V7R|V7P|V7M|V7N|V7K|V7L|V7J|V7H|V7G', N'00101', NULL, NULL, 1, NULL)
--INSERT INTO [dbo].[LogisticsZone] ([Id], [StateProvinceId], [ZipPostalCode], [Name], [CountryCode], [DisplayOrder], [DistrictId], [ZoneGroupId]) VALUES (2, NULL, N'V6G|V6E|V7X|V6C|V7Y', N'00102', NULL, NULL, 1, NULL)
--INSERT INTO [dbo].[LogisticsZone] ([Id], [StateProvinceId], [ZipPostalCode], [Name], [CountryCode], [DisplayOrder], [DistrictId], [ZoneGroupId]) VALUES (3, NULL, N'V6Z|V6B|V6A|V5L|V5K', N'00103', NULL, NULL, 1, NULL)
--INSERT INTO [dbo].[LogisticsZone] ([Id], [StateProvinceId], [ZipPostalCode], [Name], [CountryCode], [DisplayOrder], [DistrictId], [ZoneGroupId]) VALUES (4, NULL, N'V6T|V6R|V6K|V6J|V6L|V6S|V6N|V6H|V6M|V6P', N'00104', NULL, NULL, 1, NULL)
--INSERT INTO [dbo].[LogisticsZone] ([Id], [StateProvinceId], [ZipPostalCode], [Name], [CountryCode], [DisplayOrder], [DistrictId], [ZoneGroupId]) VALUES (5, NULL, N'V5T|V5Z|V5Y|V5V|V5W|V5N|V5P|V5M|V5R|V5S|V5X', N'00105', NULL, NULL, 1, NULL)
--INSERT INTO [dbo].[LogisticsZone] ([Id], [StateProvinceId], [ZipPostalCode], [Name], [CountryCode], [DisplayOrder], [DistrictId], [ZoneGroupId]) VALUES (6, NULL, N'V7B|V6V|V6X|V7C', N'00106', NULL, NULL, 1, NULL)
--INSERT INTO [dbo].[LogisticsZone] ([Id], [StateProvinceId], [ZipPostalCode], [Name], [CountryCode], [DisplayOrder], [DistrictId], [ZoneGroupId]) VALUES (7, NULL, N'V5G|V5H|V5J|V5E|V3N', N'00107', NULL, NULL, 1, NULL)
--INSERT INTO [dbo].[LogisticsZone] ([Id], [StateProvinceId], [ZipPostalCode], [Name], [CountryCode], [DisplayOrder], [DistrictId], [ZoneGroupId]) VALUES (8, NULL, N'V3H|V3J|V3K|V5A|V5B|V5C', N'00108', NULL, NULL, 1, NULL)
--INSERT INTO [dbo].[LogisticsZone] ([Id], [StateProvinceId], [ZipPostalCode], [Name], [CountryCode], [DisplayOrder], [DistrictId], [ZoneGroupId]) VALUES (9, NULL, N'V3E|V3B|V3C|V3Y|V2X|V4R|V2W', N'00109', NULL, NULL, 1, NULL)
--INSERT INTO [dbo].[LogisticsZone] ([Id], [StateProvinceId], [ZipPostalCode], [Name], [CountryCode], [DisplayOrder], [DistrictId], [ZoneGroupId]) VALUES (10, NULL, N'V3L|V3M|V6W|V6Y|V7A|V7E', N'00110', NULL, NULL, 1, NULL)
--INSERT INTO [dbo].[LogisticsZone] ([Id], [StateProvinceId], [ZipPostalCode], [Name], [CountryCode], [DisplayOrder], [DistrictId], [ZoneGroupId]) VALUES (11, NULL, N'V4C|V3W|V4E|V4G|V4K|V4L|V4M|V3V|V3T', N'00111', NULL, NULL, 1, NULL)
--INSERT INTO [dbo].[LogisticsZone] ([Id], [StateProvinceId], [ZipPostalCode], [Name], [CountryCode], [DisplayOrder], [DistrictId], [ZoneGroupId]) VALUES (12, NULL, N'V3S|V4P|V4A|V4B|V3X', N'00112', NULL, NULL, 1, NULL)
--INSERT INTO [dbo].[LogisticsZone] ([Id], [StateProvinceId], [ZipPostalCode], [Name], [CountryCode], [DisplayOrder], [DistrictId], [ZoneGroupId]) VALUES (13, NULL, N'V4N|V2Y|V3A|V2Z|V4W|V3R|V1M', N'00113', NULL, NULL, 1, NULL)
--INSERT INTO [dbo].[LogisticsZone] ([Id], [StateProvinceId], [ZipPostalCode], [Name], [CountryCode], [DisplayOrder], [DistrictId], [ZoneGroupId]) VALUES (14, NULL, N'V4S|V2V|V4X|V2T|V2S|V3G|V2R|V2P|V4Z', N'00114', NULL, NULL, 1, NULL)
--INSERT INTO [dbo].[LogisticsZone] ([Id], [StateProvinceId], [ZipPostalCode], [Name], [CountryCode], [DisplayOrder], [DistrictId], [ZoneGroupId]) VALUES (15, NULL, N'T3L|T3G|T3A|T3K|T2K|T2L|T2M|T2N|T3B|T3H|T3C|T3E|T2V|T2S|T2T|T2R|T2P|T2W|T2Y', N'00201', NULL, NULL, 2, NULL)
--INSERT INTO [dbo].[LogisticsZone] ([Id], [StateProvinceId], [ZipPostalCode], [Name], [CountryCode], [DisplayOrder], [DistrictId], [ZoneGroupId]) VALUES (16, NULL, N'T2J|T2X|T2Z|T3M|T2E|T3J|T1Y|T2A|T2B|T2C|T3N|T2G|T2H|T3S', N'00202', NULL, NULL, 2, NULL)
--INSERT INTO [dbo].[LogisticsZone] ([Id], [StateProvinceId], [ZipPostalCode], [Name], [CountryCode], [DisplayOrder], [DistrictId], [ZoneGroupId]) VALUES (17, NULL, N'T8N|T5Y|T5A|T5W|T5Z|T5C|T5B|T5H|T5J|T5K|T5E|T5G|T5X|T6V|T5L|T5M|T5N|T5R|T5P|T5V|T5S|T5T|T6M', N'00301', NULL, NULL, 3, NULL)
--INSERT INTO [dbo].[LogisticsZone] ([Id], [StateProvinceId], [ZipPostalCode], [Name], [CountryCode], [DisplayOrder], [DistrictId], [ZoneGroupId]) VALUES (18, NULL, N'T6S|T6P|T6T|T6A|T6B|T6L|T6X|T6C|T6E|T6K|T6N|T6G|T6H|T6J|T6R|T6W|T8H|T8A', N'00302', NULL, NULL, 3, NULL)
--SET IDENTITY_INSERT [dbo].[LogisticsZone] OFF
--GO


INSERT INTO [dbo].[CustomerRole] ([Name], [FreeShipping], [TaxExempt], [Active], [IsSystemRole], [SystemName]) VALUES (N'Warehouse Staff', 0, 0, 1, 0, N'WarehouseStaff')
INSERT INTO [dbo].[PermissionRecord] ([Name], [SystemName], [Category]) VALUES (N'Logistics area. Manage warehouse', N'ManageWarehouse', N'Logistics')
INSERT INTO [dbo].[PermissionRecord] ([Name], [SystemName], [Category]) VALUES (N'Logistics area. Update status', N'LogisticsUpdateStatus', N'Logistics')
